#!/bin/sh

#
# This file is released under the terms of the Artistic License.
# Please see the file LICENSE, included in this package, for details.
#
# Copyright (C) 2002-2010 Mark Wong & Open Source Development Labs, Inc.
#               2006 Rilson Nascimento
#

USE_TABLESPACES=0
while getopts "d:p:t" OPT; do
	case ${OPT} in
	d)
		DBT5DBNAME=${OPTARG}
		;;
	p)
		PORT=${OPTARG}
		;;
	t)
		USE_TABLESPACES=1
		;;
	esac
done

if [ ! "x${DBT5DBNAME}" = "x" ]; then
	DBNAMEARG="-d ${DBT5DBNAME}"
fi

if [ ! "x${PORT}" = "x" ]; then
	PORTARG="-p ${PORT}"
fi

if [ ${USE_TABLESPACES} -eq 1 ]; then
	if [ -z ${DBT5TSDIR} ]; then
		echo "DBT5TSDIR not defined for tablespace path."
		exit 1
	fi

	TS_ACCOUNT_PERMISSION_DIR=${DBT5TSDIR}/account_permission/ts
	TS_CUSTOMER_DIR=${DBT5TSDIR}/customer/ts
	TS_CUSTOMER_ACCOUNT_DIR=${DBT5TSDIR}/customer_account/ts
	TS_CUSTOMER_TAXRATE_DIR=${DBT5TSDIR}/customer_taxrate/ts
	TS_HOLDING_DIR=${DBT5TSDIR}/holding/ts
	TS_HOLDING_HISTORY_DIR=${DBT5TSDIR}/holding_history/ts
	TS_HOLDING_SUMMARY_DIR=${DBT5TSDIR}/holding_summary/ts
	TS_WATCH_ITEM_DIR=${DBT5TSDIR}/watch_item/ts
	TS_WATCH_LIST_DIR=${DBT5TSDIR}/watch_list/ts
	TS_BROKER_DIR=${DBT5TSDIR}/broker/ts
	TS_CASH_TRANSACTION_DIR=${DBT5TSDIR}/cash_transaction/ts
	TS_CHARGE_DIR=${DBT5TSDIR}/charge/ts
	TS_COMMISSION_RATE_DIR=${DBT5TSDIR}/commission_rate/ts
	TS_SETTLEMENT_DIR=${DBT5TSDIR}/settlement/ts
	TS_TRADE_DIR=${DBT5TSDIR}/trade/ts
	TS_TRADE_HISTORY_DIR=${DBT5TSDIR}/trade_history/ts
	TS_TRADE_REQUEST_DIR=${DBT5TSDIR}/trade_request/ts
	TS_TRADE_TYPE_DIR=${DBT5TSDIR}/trade_type/ts
	TS_COMPANY_DIR=${DBT5TSDIR}/company/ts
	TS_COMPANY_COMPETITOR_DIR=${DBT5TSDIR}/company_competitor/ts
	TS_DAILY_MARKET_DIR=${DBT5TSDIR}/daily_market/ts
	TS_EXCHANGE_DIR=${DBT5TSDIR}/exchange/ts
	TS_FINANCIAL_DIR=${DBT5TSDIR}/financial/ts
	TS_INDUSTRY_DIR=${DBT5TSDIR}/industry/ts
	TS_LAST_TRADE_DIR=${DBT5TSDIR}/last_trade/ts
	TS_NEWS_ITEM_DIR=${DBT5TSDIR}/news_item/ts
	TS_NEWS_XREF_DIR=${DBT5TSDIR}/news_xref/ts
	TS_SECTOR_DIR=${DBT5TSDIR}/sector/ts
	TS_SECURITY_DIR=${DBT5TSDIR}/security/ts
	TS_ADDRESS_DIR=${DBT5TSDIR}/address/ts
	TS_STATUS_TYPE_DIR=${DBT5TSDIR}/status_type/ts
	TS_TAXRATE_DIR=${DBT5TSDIR}/taxrate/ts
	TS_ZIP_CODE_DIR=${DBT5TSDIR}/zip_code/ts

	#
	# Creating 'ts' subdirectories because PostgreSQL doesn't like that
	# 'lost+found' directory if a device was mounted at
	# '${DBT5TSDIR}/warehouse'.
	#
	mkdir -p ${TS_ACCOUNT_PERMISSION_DIR} || exit 1
	mkdir -p ${TS_CUSTOMER_DIR} || exit 1
	mkdir -p ${TS_CUSTOMER_ACCOUNT_DIR} || exit 1
	mkdir -p ${TS_CUSTOMER_TAXRATE_DIR} || exit 1
	mkdir -p ${TS_HOLDING_DIR} || exit 1
	mkdir -p ${TS_HOLDING_HISTORY_DIR} || exit 1
	mkdir -p ${TS_HOLDING_SUMMARY_DIR} || exit 1
	mkdir -p ${TS_WATCH_ITEM_DIR} || exit 1
	mkdir -p ${TS_WATCH_LIST_DIR} || exit 1
	mkdir -p ${TS_BROKER_DIR} || exit 1
	mkdir -p ${TS_CASH_TRANSACTION_DIR} || exit 1
	mkdir -p ${TS_CHARGE_DIR} || exit 1
	mkdir -p ${TS_COMMISSION_RATE_DIR} || exit 1
	mkdir -p ${TS_SETTLEMENT_DIR} || exit 1
	mkdir -p ${TS_TRADE_DIR} || exit 1
	mkdir -p ${TS_TRADE_HISTORY_DIR} || exit 1
	mkdir -p ${TS_TRADE_REQUEST_DIR} || exit 1
	mkdir -p ${TS_TRADE_TYPE_DIR} || exit 1
	mkdir -p ${TS_COMPANY_DIR} || exit 1
	mkdir -p ${TS_COMPANY_COMPETITOR_DIR} || exit 1
	mkdir -p ${TS_DAILY_MARKET_DIR} || exit 1
	mkdir -p ${TS_EXCHANGE_DIR} || exit 1
	mkdir -p ${TS_FINANCIAL_DIR} || exit 1
	mkdir -p ${TS_INDUSTRY_DIR} || exit 1
	mkdir -p ${TS_LAST_TRADE_DIR} || exit 1
	mkdir -p ${TS_NEWS_ITEM_DIR} || exit 1
	mkdir -p ${TS_NEWS_XREF_DIR} || exit 1
	mkdir -p ${TS_SECTOR_DIR} || exit 1
	mkdir -p ${TS_SECURITY_DIR} || exit 1
	mkdir -p ${TS_ADDRESS_DIR} || exit 1
	mkdir -p ${TS_STATUS_TYPE_DIR} || exit 1
	mkdir -p ${TS_TAXRATE_DIR} || exit 1
	mkdir -p ${TS_ZIP_CODE_DIR} || exit 1

	TS_ACCOUNT_PERMISSION="TABLESPACE dbt5_account_permission"
	TS_CUSTOMER="TABLESPACE dbt5_customer"
	TS_CUSTOMER_ACCOUNT="TABLESPACE dbt5_customer_account"
	TS_CUSTOMER_TAXRATE="TABLESPACE dbt5_customer_taxrate"
	TS_HOLDING="TABLESPACE dbt5_holding"
	TS_HOLDING_HISTORY="TABLESPACE dbt5_holding_history"
	TS_HOLDING_SUMMARY="TABLESPACE dbt5_holding_summary"
	TS_WATCH_ITEM="TABLESPACE dbt5_watch_item"
	TS_WATCH_LIST="TABLESPACE dbt5_watch_list"
	TS_BROKER="TABLESPACE dbt5_broker"
	TS_CASH_TRANSACTION="TABLESPACE dbt5_cash_transaction"
	TS_CHARGE="TABLESPACE dbt5_charge"
	TS_COMMISSION_RATE="TABLESPACE dbt5_commission_rate"
	TS_SETTLEMENT="TABLESPACE dbt5_settlement"
	TS_TRADE="TABLESPACE dbt5_trade"
	TS_TRADE_HISTORY="TABLESPACE dbt5_trade_history"
	TS_TRADE_REQUEST="TABLESPACE dbt5_trade_request"
	TS_TRADE_TYPE="TABLESPACE dbt5_trade_type"
	TS_COMPANY="TABLESPACE dbt5_company"
	TS_COMPANY_COMPETITOR="TABLESPACE dbt5_company_competitor"
	TS_DAILY_MARKET="TABLESPACE dbt5_daily_market"
	TS_EXCHANGE="TABLESPACE dbt5_exchange"
	TS_FINANCIAL="TABLESPACE dbt5_financial"
	TS_INDUSTRY="TABLESPACE dbt5_industry"
	TS_LAST_TRADE="TABLESPACE dbt5_last_trade"
	TS_NEWS_ITEM="TABLESPACE dbt5_news_item"
	TS_NEWS_XREF="TABLESPACE dbt5_news_xref"
	TS_SECTOR="TABLESPACE dbt5_sector"
	TS_SECURITY="TABLESPACE dbt5_security"
	TS_ADDRESS="TABLESPACE dbt5_address"
	TS_STATUS_TYPE="TABLESPACE dbt5_status_type"
	TS_TAXRATE="TABLESPACE dbt5_taxrate"
	TS_ZIP_CODE="TABLESPACE dbt5_zip_code"

	#
	# Don't need to '|| exit 1' in case the tablespaces do not already exist.
	#
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_ACCOUNT_PERMISSION};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_ACCOUNT_PERMISSION} LOCATION '${TS_ACCOUNT_PERMISSION_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_CUSTOMER};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_CUSTOMER} LOCATION '${TS_CUSTOMER_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_CUSTOMER_ACCOUNT};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_CUSTOMER_ACCOUNT} LOCATION '${TS_CUSTOMER_ACCOUNT_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_CUSTOMER_TAXRATE};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_CUSTOMER_TAXRATE} LOCATION '${TS_CUSTOMER_TAXRATE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_HOLDING};"
	psql ${PORTARG} -e ${DBNAMEARG} -c "CREATE ${TS_HOLDING} LOCATION '${TS_HOLDING_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_HOLDING_HISTORY};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_HOLDING_HISTORY} LOCATION '${TS_HOLDING_HISTORY_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_HOLDING_SUMMARY};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_HOLDING_SUMMARY} LOCATION '${TS_HOLDING_SUMMARY_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_WATCH_ITEM};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_WATCH_ITEM} LOCATION '${TS_WATCH_ITEM_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_WATCH_LIST};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_WATCH_LIST} LOCATION '${TS_WATCH_LIST_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_BROKER};"
	psql ${PORTARG} -e ${DBNAMEARG} -c "CREATE ${TS_BROKER} LOCATION '${TS_BROKER_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_CASH_TRANSACTION};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_CASH_TRANSACTION} LOCATION '${TS_CASH_TRANSACTION_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_CHARGE};"
	psql ${PORTARG} -e ${DBNAMEARG} -c "CREATE ${TS_CHARGE} LOCATION '${TS_CHARGE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_COMMISSION_RATE};"
	psql ${PORTARG} -e ${DBNAMEARG} -c "CREATE ${TS_COMMISSION_RATE} LOCATION '${TS_COMMISSION_RATE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_SETTLEMENT};"
	psql ${PORTARG} -e ${DBNAMEARG} -c "CREATE ${TS_SETTLEMENT} LOCATION '${TS_SETTLEMENT_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_TRADE};"
	psql ${PORTARG} -e ${DBNAMEARG} -c "CREATE ${TS_TRADE} LOCATION '${TS_TRADE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_TRADE_HISTORY};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_TRADE_HISTORY} LOCATION '${TS_TRADE_HISTORY_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_TRADE_REQUEST};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_TRADE_REQUEST} LOCATION '${TS_TRADE_REQUEST_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_TRADE_TYPE};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_TRADE_TYPE} LOCATION '${TS_TRADE_TYPE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_COMPANY};"
	psql ${PORTARG} -e ${DBNAMEARG} -c "CREATE ${TS_COMPANY} LOCATION '${TS_COMPANY_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_COMPANY_COMPETITOR};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_COMPANY_COMPETITOR} LOCATION '${TS_COMPANY_COMPETITOR_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_DAILY_MARKET};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_DAILY_MARKET} LOCATION '${TS_DAILY_MARKET_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_EXCHANGE};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_EXCHANGE} LOCATION '${TS_EXCHANGE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_FINANCIAL};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_FINANCIAL} LOCATION '${TS_FINANCIAL_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_INDUSTRY};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_INDUSTRY} LOCATION '${TS_INDUSTRY_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_LAST_TRADE};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_LAST_TRADE} LOCATION '${TS_LAST_TRADE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_NEWS_ITEM};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_NEWS_ITEM} LOCATION '${TS_NEWS_ITEM_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_NEWS_XREF};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_NEWS_XREF} LOCATION '${TS_NEWS_XREF_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_SECTOR};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_SECTOR} LOCATION '${TS_SECTOR_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_SECURITY};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_SECURITY} LOCATION '${TS_SECURITY_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_ADDRESS};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_ADDRESS} LOCATION '${TS_ADDRESS_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_STATUS_TYPE};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_STATUS_TYPE} LOCATION '${TS_STATUS_TYPE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_TAXRATE};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_TAXRATE} LOCATION '${TS_TAXRATE_DIR}';"
	psql ${PORTARG} -e ${DBNAMEARG} -c "DROP ${TS_ZIP_CODE};"
	psql ${PORTARG} -e ${DBNAMEARG} \
			-c "CREATE ${TS_ZIP_CODE} LOCATION '${TS_ZIP_CODE_DIR}';"
fi

# Creating sequence for Trade tables, t_id field

psql ${PORTARG} -e ${DBNAMEARG} \
		-c "CREATE SEQUENCE seq_trade_id;" || exit 1

# Creating domains to meta-type definitions (clause 2.2.3)

#psql ${PORTARG} -e ${DBNAMEARG} \
#		-c "CREATE DOMAIN S_COUNT_T AS BIGINT;" || exit 1
#psql ${PORTARG} -e ${DBNAMEARG} \
#		-c "CREATE DOMAIN TRADE_T AS BIGINT NULL;" || exit 1
#psql ${PORTARG} -e ${DBNAMEARG} \
#		-c "CREATE DOMAIN IDENT_T AS BIGINT NULL;" || exit 1
#psql ${PORTARG} -e ${DBNAMEARG} \
#		-c "CREATE DOMAIN BALANCE_T AS NUMERIC(12,2);" || exit 1
#psql ${PORTARG} -e ${DBNAMEARG} \
#		-c "CREATE DOMAIN S_PRICE_T AS NUMERIC(8,2);" || exit 1
#psql ${PORTARG} -e ${DBNAMEARG} \
#		-c "CREATE DOMAIN S_QTY_T AS INTEGER NULL;" || exit 1
#psql ${PORTARG} -e ${DBNAMEARG} \
#		-c "CREATE DOMAIN VALUE_T AS NUMERIC(10,2) NULL;" || exit 1
#psql ${PORTARG} -e ${DBNAMEARG} \
#		-c "CREATE DOMAIN FIN_AGG_T AS NUMERIC(15,2) NOT NULL;" || exit 1

# Note: when the specification states CHAR(n), the implementer can choose 
#   whether it pads strings with spaces to always have the maximum length,
#	or whether it doesn't pad strings (clause 2.2.2.1)

# Customer Tables

# Clause 2.2.5.1
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE account_permission (
    ap_ca_id BIGINT NOT NULL,
    ap_acl VARCHAR(4) NOT NULL,
    ap_tax_id VARCHAR(20) NOT NULL,
    ap_l_name VARCHAR(30) NOT NULL,
    ap_f_name VARCHAR(30) NOT NULL, 
    CONSTRAINT pk_account_permission PRIMARY KEY (ap_ca_id, ap_tax_id))
    ${TS_ACCOUNT_PERMISSION};
" || exit 1	

# Clause 2.2.5.2
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE customer (
    c_id BIGINT NOT NULL,
    c_tax_id VARCHAR(20) NOT NULL,
    c_st_id VARCHAR(4) NOT NULL,
    c_l_name VARCHAR(30) NOT NULL,
    c_f_name VARCHAR(30) NOT NULL,
    c_m_name CHAR(1),
    c_gndr CHAR(1),
    c_tier SMALLINT NOT NULL,
    c_dob DATE NOT NULL,
    c_ad_id BIGINT NOT NULL,
    c_ctry_1 VARCHAR(3),
    c_area_1 VARCHAR(3),
    c_local_1 VARCHAR(10),
    c_ext_1 VARCHAR(5),
    c_ctry_2 VARCHAR(3),
    c_area_2 VARCHAR(3),
    c_local_2 VARCHAR(10),
    c_ext_2 VARCHAR(5),
    c_ctry_3 VARCHAR(3),
    c_area_3 VARCHAR(3),
    c_local_3 VARCHAR(10),
    c_ext_3 VARCHAR(5),
    c_email_1 VARCHAR(50),
    c_email_2 VARCHAR(50),
    CONSTRAINT pk_customer PRIMARY KEY (c_id))
    ${TS_CUSTOMER};
" || exit 1

# Clause 2.2.5.3
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE customer_account (
    ca_id BIGINT NOT NULL,
    ca_b_id BIGINT NOT NULL,
    ca_c_id BIGINT NOT NULL,
    ca_name VARCHAR(50),
    ca_tax_st SMALLINT NOT NULL,
    ca_bal NUMERIC(12,2) NOT NULL,
    CONSTRAINT pk_customer_account PRIMARY KEY (ca_id)) 
    ${TS_CUSTOMER_ACCOUNT};
" || exit 1

# Clause 2.2.5.4
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE customer_taxrate (
    cx_tx_id VARCHAR(4) NOT NULL,
    cx_c_id BIGINT NOT NULL,
    CONSTRAINT pk_customer_taxrate PRIMARY KEY (cx_tx_id, cx_c_id))
    ${TS_CUSTOMER_TAXRATE};
" || exit 1

# Clause 2.2.5.5
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE holding (
    h_t_id BIGINT NOT NULL,
    h_ca_id BIGINT NOT NULL,
    h_s_symb VARCHAR(15) NOT NULL,
    h_dts TIMESTAMP NOT NULL,
    h_price NUMERIC(8,2) NOT NULL,
    h_qty INTEGER NOT NULL,
    CONSTRAINT pk_holding PRIMARY KEY (h_t_id))
    ${TS_HOLDING};
" || exit 1

# Clause 2.2.5.6
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE holding_history (
    hh_h_t_id BIGINT NOT NULL,
    hh_t_id BIGINT NOT NULL,
    hh_before_qty INTEGER NOT NULL,
    hh_after_qty INTEGER NOT NULL,
    CONSTRAINT pk_holding_history PRIMARY KEY (hh_h_t_id, hh_t_id))
    ${TS_HOLDING_HISTORY};
" || exit 1

# Clause 2.2.5.7
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE holding_summary (
    hs_ca_id BIGINT NOT NULL,
    hs_s_symb VARCHAR(15) NOT NULL,
    hs_qty INTEGER NOT NULL,
    CONSTRAINT pk_holding_summary PRIMARY KEY (hs_ca_id, hs_s_symb))
    ${TS_HOLDING_SUMMARY};
" || exit 1

# Clause 2.2.5.8
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE watch_item (
    wi_wl_id BIGINT NOT NULL,
    wi_s_symb VARCHAR(15) NOT NULL,
    CONSTRAINT pk_watch_item PRIMARY KEY (wi_wl_id, wi_s_symb))
    ${TS_WATCH_ITEM};
" || exit 1

# Clause 2.2.5.9
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE watch_list (
    wl_id BIGINT NOT NULL,
    wl_c_id BIGINT NOT NULL,
    CONSTRAINT pk_watch_list PRIMARY KEY (wl_id))
    ${TS_WATCH_LIST};
" || exit 1

# Broker Tables

# Clause 2.2.6.1
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE broker (
    b_id BIGINT NOT NULL,
    b_st_id VARCHAR(4) NOT NULL,
    b_name VARCHAR(100) NOT NULL,
    b_num_trades INTEGER NOT NULL,
    b_comm_total NUMERIC(12,2) NOT NULL,
    CONSTRAINT pk_broker PRIMARY KEY (b_id))
    ${TS_BROKER};
" || exit 1

# Clause 2.2.6.2
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE cash_transaction (
    ct_t_id BIGINT NOT NULL,
    ct_dts TIMESTAMP NOT NULL,
    ct_amt NUMERIC(10,2) NOT NULL,
    ct_name VARCHAR(100),
    CONSTRAINT pk_cash_transaction PRIMARY KEY (ct_t_id))
    ${TS_CASH_TRANSACTION};
" || exit 1

# Clause 2.2.6.3
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE charge (
    ch_tt_id VARCHAR(3) NOT NULL,
    ch_c_tier SMALLINT,
    ch_chrg NUMERIC(10,2),
    CONSTRAINT pk_charge PRIMARY KEY (ch_tt_id, ch_c_tier))
    ${TS_CHARGE};
" || exit 1

# Clause 2.2.6.4
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE commission_rate (
    cr_c_tier SMALLINT NOT NULL,
    cr_tt_id VARCHAR(3) NOT NULL,
    cr_ex_id VARCHAR(6) NOT NULL,
    cr_from_qty INTEGER NOT NULL,
    cr_to_qty INTEGER NOT NULL,
    cr_rate NUMERIC(5, 2) NOT NULL,
    CONSTRAINT pk_commission_rate PRIMARY KEY (cr_c_tier, cr_tt_id, cr_ex_id, cr_from_qty))
    ${TS_COMMISSION_RATE};
" || exit 1

# Clause 2.2.6.5
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE settlement (
    se_t_id BIGINT NOT NULL,
    se_cash_type VARCHAR(40) NOT NULL,
    se_cash_due_date DATE NOT NULL,
    se_amt NUMERIC(10,2) NOT NULL,
    CONSTRAINT pk_settlement PRIMARY KEY (se_t_id))
    ${TS_SETTLEMENT};
" || exit 1

# Clause 2.2.6.6
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE trade (
    t_id BIGINT NOT NULL,
    t_dts TIMESTAMP NOT NULL,
    t_st_id VARCHAR(4) NOT NULL,
    t_tt_id VARCHAR(3) NOT NULL,
    t_is_cash BOOLEAN NOT NULL,
    t_s_symb VARCHAR(15) NOT NULL,
    t_qty INTEGER NOT NULL,
    t_bid_price NUMERIC(8,2) NOT NULL,
    t_ca_id BIGINT NOT NULL,
    t_exec_name VARCHAR(64) NOT NULL,
    t_trade_price NUMERIC(8,2),
    t_chrg NUMERIC(10,2) NOT NULL,
    t_comm NUMERIC(10,2) NOT NULL,
    t_tax NUMERIC(10,2) NOT NULL,
    t_lifo BOOLEAN NOT NULL,
    CONSTRAINT pk_trade PRIMARY KEY (t_id))
    ${TS_TRADE};
" || exit 1

# Clause 2.2.6.7
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE trade_history (
    th_t_id BIGINT NOT NULL,
    th_dts TIMESTAMP NOT NULL,
    th_st_id VARCHAR(4) NOT NULL,
    CONSTRAINT pk_trade_history PRIMARY KEY (th_t_id, th_st_id))
    ${TS_TRADE_HISTORY};
" || exit 1

# Clause 2.2.6.8
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE trade_request (
    tr_t_id BIGINT NOT NULL,
    tr_tt_id VARCHAR(3) NOT NULL,
    tr_s_symb VARCHAR(15) NOT NULL,
    tr_qty INTEGER NOT NULL,
    tr_bid_price NUMERIC(8,2) NOT NULL,
    tr_b_id BIGINT NOT NULL,
    CONSTRAINT pk_trade_request PRIMARY KEY (tr_t_id))
    ${TS_TRADE_REQUEST};
" || exit 1

# Clause 2.2.6.9
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE trade_type (
    tt_id VARCHAR(3) NOT NULL,
    tt_name VARCHAR(12) NOT NULL,
    tt_is_sell BOOLEAN NOT NULL,
    tt_is_mrkt BOOLEAN NOT NULL,
    CONSTRAINT pk_trade_type PRIMARY KEY (tt_id))
    ${TS_TRADE_TYPE};
" || exit 1

# Market Tables

# Clause 2.2.7.1
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE company (
    co_id BIGINT NOT NULL,
    co_st_id VARCHAR(4) NOT NULL,
    co_name VARCHAR(60) NOT NULL,
    co_in_id VARCHAR(2) NOT NULL,
    co_sp_rate VARCHAR(4) NOT NULL,
    co_ceo VARCHAR(100) NOT NULL,
    co_ad_id BIGINT NOT NULL,
    co_desc VARCHAR(150) NOT NULL,
    co_open_date DATE NOT NULL,
    CONSTRAINT pk_company PRIMARY KEY (co_id))
    ${TS_COMPANY};
" || exit 1

# Clause 2.2.7.2
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE company_competitor (
    cp_co_id BIGINT NOT NULL,
    cp_comp_co_id BIGINT NOT NULL,
    cp_in_id VARCHAR(2) NOT NULL,
    CONSTRAINT pk_company_competitor PRIMARY KEY (cp_co_id, cp_comp_co_id, cp_in_id))
    ${TS_COMPANY_COMPETITOR};
" || exit 1

# Clause 2.2.7.3
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE daily_market (
    dm_date DATE NOT NULL,
    dm_s_symb VARCHAR(15) NOT NULL,
    dm_close NUMERIC(8,2) NOT NULL,
    dm_high NUMERIC(8,2) NOT NULL,
    dm_low NUMERIC(8,2) NOT NULL,
    dm_vol BIGINT NOT NULL,
    CONSTRAINT pk_daily_market PRIMARY KEY (dm_date, dm_s_symb))
    ${TS_DAILY_MARKET};
" || exit 1

# Clause 2.2.7.4
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE exchange (
    ex_id VARCHAR(6) NOT NULL,
    ex_name VARCHAR(100) NOT NULL,
    ex_num_symb INTEGER NOT NULL,
    ex_open INTEGER NOT NULL,
    ex_close INTEGER NOT NULL,
    ex_desc VARCHAR(150),
    ex_ad_id BIGINT NOT NULL,
    CONSTRAINT pk_exchange PRIMARY KEY (ex_id))
    ${TS_EXCHANGE};
" || exit 1

# Clause 2.2.7.5
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE financial (
    fi_co_id BIGINT NOT NULL,
    fi_year INTEGER NOT NULL,
    fi_qtr SMALLINT NOT NULL,
    fi_qtr_start_date DATE NOT NULL,
    fi_revenue NUMERIC(15,2) NOT NULL,
    fi_net_earn NUMERIC(15,2) NOT NULL,
    fi_basic_eps NUMERIC(10,2) NOT NULL,
    fi_dilut_eps NUMERIC(10,2) NOT NULL,
    fi_margin NUMERIC(10,2) NOT NULL,
    fi_inventory NUMERIC(15,2) NOT NULL,
    fi_assets NUMERIC(15,2) NOT NULL,
    fi_liability NUMERIC(15,2) NOT NULL,
    fi_out_basic BIGINT NOT NULL,
    fi_out_dilut BIGINT NOT NULL,
    CONSTRAINT pk_financial PRIMARY KEY (fi_co_id, fi_year, fi_qtr))
    ${TS_FINANCIAL};
" || exit 1

# Clause 2.2.7.6
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE industry (
    in_id VARCHAR(2) NOT NULL,
    in_name VARCHAR(50) NOT NULL,
    in_sc_id VARCHAR(2) NOT NULL,
    CONSTRAINT pk_industry PRIMARY KEY (in_id))
    ${TS_INDUSTRY};
" || exit 1

# Clause 2.2.7.7
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE last_trade (
    lt_s_symb VARCHAR(15) NOT NULL,
    lt_dts TIMESTAMP NOT NULL,
    lt_price NUMERIC(8,2) NOT NULL,
    lt_open_price NUMERIC(8,2) NOT NULL,
    lt_vol BIGINT,
    CONSTRAINT pk_last_trade PRIMARY KEY (lt_s_symb))
    ${TS_LAST_TRADE};
" || exit 1

# Clause 2.2.7.8
# FIXME: The NI_ITEM field may be either LOB(100000) or
#	LOB_Ref, which is a reference to a LOB(100000) object
#	stored outside the table. 
#	TEXT seems to be simpler to handle

psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE news_item (
    ni_id BIGINT NOT NULL,
    ni_headline VARCHAR(80) NOT NULL,
    ni_summary VARCHAR(255) NOT NULL,
    ni_item VARCHAR(1000) NOT NULL,
    ni_dts TIMESTAMP NOT NULL,
    ni_source VARCHAR(30) NOT NULL,
    ni_author VARCHAR(30),
    CONSTRAINT pk_news_item PRIMARY KEY (ni_id))
    ${TS_NEWS_ITEM};
" || exit 1

# Clause 2.2.7.9
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE news_xref (
    nx_ni_id BIGINT NOT NULL,
    nx_co_id BIGINT NOT NULL,
    CONSTRAINT pk_news_xref PRIMARY KEY (nx_ni_id, nx_co_id))
    ${TS_NEWS_XREF};
" || exit 1

# Clause 2.2.7.10
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE sector (
    sc_id VARCHAR(2) NOT NULL,
    sc_name VARCHAR(30) NOT NULL,
    CONSTRAINT pk_sector PRIMARY KEY (sc_id))
    ${TS_SECTOR};
" || exit 1

# Clause 2.2.7.11
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE security (
    s_symb VARCHAR(15) NOT NULL,
    s_issue VARCHAR(6) NOT NULL,
    s_st_id VARCHAR(4) NOT NULL,
    s_name VARCHAR(70) NOT NULL,
    s_ex_id VARCHAR(6) NOT NULL,
    s_co_id BIGINT NOT NULL,
    s_num_out BIGINT NOT NULL,
    s_start_date DATE NOT NULL,
    s_exch_date DATE NOT NULL,
    s_pe NUMERIC(10,2) NOT NULL,
    s_52wk_high NUMERIC(8,2) NOT NULL,
    s_52wk_high_date DATE NOT NULL,
    s_52wk_low NUMERIC(8,2) NOT NULL,
    s_52wk_low_date DATE NOT NULL,
    s_dividend NUMERIC(10,2) NOT NULL,
    s_yield NUMERIC(5,2) NOT NULL,
    CONSTRAINT pk_security PRIMARY KEY (s_symb))
    ${TS_SECURITY};
" || exit 1

# Dimension Tables

# Clause 2.2.8.1
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE address (
    ad_id BIGINT NOT NULL,
    ad_line1 VARCHAR(80),
    ad_line2 VARCHAR(80),
    ad_zc_code VARCHAR(12) NOT NULL,
    ad_ctry VARCHAR(80),
    CONSTRAINT pk_address PRIMARY KEY (ad_id))
    ${TS_ADDRESS};
" || exit 1

# Clause 2.2.8.2
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE status_type (
    st_id VARCHAR(4) NOT NULL,
    st_name VARCHAR(10) NOT NULL,
    CONSTRAINT pk_status_type PRIMARY KEY (st_id))
    ${TS_STATUS_TYPE};
" || exit 1

# Clause 2.2.8.3
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE taxrate (
    tx_id VARCHAR(4) NOT NULL,
    tx_name VARCHAR(50) NOT NULL,
    tx_rate NUMERIC(6,5) NOT NULL,
    CONSTRAINT pk_taxrate PRIMARY KEY (tx_id))
    ${TS_TAXRATE};
" || exit 1

# Clause 2.2.8.4
psql ${PORTARG} -e ${DBNAMEARG} -c "
CREATE FOREIGN TABLE zip_code (
    zc_code VARCHAR(12) NOT NULL,
    zc_town VARCHAR(80) NOT NULL,
    zc_div VARCHAR(80) NOT NULL,
    CONSTRAINT pk_zip_code PRIMARY KEY (zc_code))
    ${TS_ZIP_CODE};
" || exit 1

exit 0
